In any engineering project, you can’t avoid testing your product against reality. A friend who once worked at a car design center told me that throughout each development cycle, in adding to a component-testing program with test-rigs and simulations, they would drive each newly-designed automobile down “Dunmore Lane”. This local road was infamous for its challenging terrain, reverse-camber bends and general state of disrepair. In other words, it was typical of reality. Experience told the engineers that many problems emerged only at this stage. However good the simulations, the engineers had to test the car’s mettle on the road’s decrepit tarmac, sudden bumps and deadly potholes, with a variety of loads in the car. Eventually, insurance and the local police insisted that this should cease, so the entire road was digitally mapped in meticulous detail and re-created on a test track, even with the potholes and puddles.

The result was that throughout development, the engineers measured continuously how the car responded under the worst of real world conditions, giving them far more confidence that the finished car would be up to the task. The parallels with software development are obvious and yet, when dealing with large databases, testing them out regularly on the metaphorical Dunmore Lane, while vital, is not straightforward.

During development, it’s quite expensive to set up a database in the same state as production, each time you want to run a test. This means that realistic performance testing of a database tends to happen late in the development cycle, if at all. Typically, during functional testing the database will be mocked, and any performance tests will require creating the database from the scripts in source control and BCPing in a “reasonable” amount of test data. Good, but not sufficient and there are likely to be very nasty performance surprises when the database finally hits the database equivalent of Dunmore Lane, i.e. production.

By doing regular performance testing with a realistic workload, with a realistic set of data, you get the same instant feedback as the automobile engineers. In practice, this can be very hard to do for the larger databases. To make it quicker and easier, Redgate is experimenting in “the labs” with a system code-named “SQL Clone”. It creates an instant clone of the target database, basically a virtual snapshot or volume shadow copy (standard technology, built into Windows). Taking the initial clone takes about as long as a backup but reproducing the clone, any number of times, is very quick. This means, for example, that it becomes viable for developers to test each incremental migration against a ‘clone’ of the production database, and then simply dispose of the result and quickly reproduce the clone afresh for the next test. At the end of each day, the team could run a suite of integration tests, and performance tests, against a ‘real database’ rather than some cut-down version. If they see issues, they know that the latest modifications ruined the handling or performance. For the developer, the suspects are all lined up in source control.

I’d be interested to hear of any techniques that people currently use to take their databases for a regular spin down “Dunmore Lane”, throughout the development cycle.